using System;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;

namespace WegasManagementSystem.DAL
{
    /// <summary>
    /// 数据库连接与操作工具类
    /// </summary>
    public static class DBConnection
    {
        /// <summary>
        /// 从配置文件中获取连接字符串
        /// </summary>
        private static string ConnectionString = ConfigurationManager.ConnectionStrings["WegasConnection"].ConnectionString;

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns>MySqlConnection对象</returns>
        public static MySqlConnection GetConnection()
        {
            MySqlConnection connection = new MySqlConnection(ConnectionString);
            return connection;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <returns>是否成功打开</returns>
        public static bool OpenConnection(MySqlConnection connection)
        {
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show($"连接数据库失败：{ex.Message}", "错误", 
                    System.Windows.Forms.MessageBoxButtons.OK, 
                    System.Windows.Forms.MessageBoxIcon.Error);
                return false;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="connection">数据库连接</param>
        public static void CloseConnection(MySqlConnection connection)
        {
            try
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            catch
            {
                // 忽略关闭连接时的错误
            }
        }

        /// <summary>
        /// 执行SQL命令返回影响的行数
        /// </summary>
        /// <param name="cmdText">SQL命令文本</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] parameters)
        {
            int result = 0;
            using (MySqlConnection connection = GetConnection())
            {
                if (OpenConnection(connection))
                {
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, connection))
                    {
                        cmd.CommandType = cmdType;
                        if (parameters != null && parameters.Length > 0)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        result = cmd.ExecuteNonQuery();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 执行SQL命令返回第一行第一列的值
        /// </summary>
        /// <param name="cmdText">SQL命令文本</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>第一行第一列的值</returns>
        public static object ExecuteScalar(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] parameters)
        {
            object result = null;
            using (MySqlConnection connection = GetConnection())
            {
                if (OpenConnection(connection))
                {
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, connection))
                    {
                        cmd.CommandType = cmdType;
                        if (parameters != null && parameters.Length > 0)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        result = cmd.ExecuteScalar();
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// 执行SQL命令返回DataTable
        /// </summary>
        /// <param name="cmdText">SQL命令文本</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>DataTable对象</returns>
        public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection connection = GetConnection())
            {
                if (OpenConnection(connection))
                {
                    using (MySqlCommand cmd = new MySqlCommand(cmdText, connection))
                    {
                        cmd.CommandType = cmdType;
                        if (parameters != null && parameters.Length > 0)
                        {
                            cmd.Parameters.AddRange(parameters);
                        }
                        using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                        {
                            adapter.Fill(dt);
                        }
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL命令返回DataReader
        /// </summary>
        /// <param name="cmdText">SQL命令文本</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>MySqlDataReader对象</returns>
        public static MySqlDataReader ExecuteReader(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] parameters)
        {
            MySqlConnection connection = GetConnection();
            if (OpenConnection(connection))
            {
                MySqlCommand cmd = new MySqlCommand(cmdText, connection);
                cmd.CommandType = cmdType;
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                
                // CommandBehavior.CloseConnection参数指示在关闭reader时自动关闭连接
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            return null;
        }
    }
} 